package com.zengdada.authority.utils;

import com.alibaba.fastjson.JSONArray;
import com.zengdada.authority.utils.excel.export.ExportExcel;
import lombok.Getter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.awt.*;
import java.awt.geom.Rectangle2D;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.List;
import java.util.Map.Entry;
import java.util.function.Predicate;
import java.util.function.UnaryOperator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {

    private final static String regxForHtml = "<([^>]*)>"; // 过滤所有以<开头以>结尾的标签

    /**
     * 把List<LinkedHashMap<String, Object>> 或者 JSONArray 结构的数据转化成Excel 然后导出
     *
     * @param result
     * @param out
     * @return int 0:表示数据有问题 1：表示成功输出
     * @throws IOException
     */
    /**
     * // TODO
     *
     * @param result
     * @param out
     * @return {@link boolean}
     * @author 12614
     * @date 2020-05-23 2:00
     */
    public boolean exportExcel(List<?> result, OutputStream out) throws IOException {
        // A.处理result的数据结构
        int len = result.size();
        if (len <= 0) {
            return false;
        }
        Workbook wb = null;
        // System.err.println(len);
        try {
            Map<String, Object> firstDate = (Map<String, Object>) result.get(0);
            List<String> header = new ArrayList<>();// 得到头部
            for (Entry<String, Object> value : firstDate.entrySet()) {
                header.add(value.getKey());
            }

            // B.创建Excel工作簿
            wb = new HSSFWorkbook();

            int number = (len - 1) / 65535 + 1;
            for (int i = 0; i < number; i++) {
                // 创建一个工作表sheet
                Sheet sheet = wb.createSheet("Detailed" + i);
                // 创建第一行
                Row row = sheet.createRow(0);
                // 设置表头样式
                HSSFFont fontStyle = (HSSFFont) wb.createFont();
                fontStyle.setFontName("Microsoft YaHei");
                fontStyle.setBold(true);
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 水平居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
                cellStyle.setFont(fontStyle);
                Cell cell;
                for (int f = 0; f < header.size(); f++) {
                    cell = row.createCell(f);
                    cell.setCellValue(header.get(f));
                    cell.setCellStyle(cellStyle);
                    sheet.setColumnWidth(f, 255 * 25);
                }
                sheet.createFreezePane(0, 1, 0, 1);// 冻结第一行

                CellHelper helper = new CellHelper(wb);// 得到样式帮助对象
                // 遍历数据，写入工作簿
                int max = (1 + i) == number ? len - i * 65535 : 65535;
                Row rows;
                for (int j = 0; j < max; j++) {
                    Map<String, Object> item = (Map<String, Object>) result.get(j + i * 65535);
                    rows = sheet.createRow(j + 1);
                    int a = 0;
                    for (Entry<String, Object> value : item.entrySet()) {
                        createCell(value.getValue(), rows, a++, helper);
                    }
                }
            }
            wb.write(out);
        } finally {
            if (wb != null) {
                wb.close();
            }
            if (out != null) {
                out.flush();
                out.close();
            }
        }
        return true;
    }


    /**
     * @param obj       需要设置的值
     * @param row       创建的行
     * @param cellIndex 第几行
     */
    protected void createCell(Object obj, Row row, int cellIndex, CellHelper helper) {
        Cell cell = row.createCell(cellIndex);
        if (obj instanceof String) {
            String value = filterHtml((String) obj);
            cell.setCellValue(value);
            cell.setCellStyle(helper.mWrittenStyle);
        } else if (obj instanceof Integer) {
            cell.setCellValue((Integer) obj);
            cell.setCellStyle(helper.mNumberStyle);
        } else if (obj instanceof BigDecimal) {
            BigDecimal temp = (BigDecimal) obj;
            cell.setCellValue(temp.doubleValue());
            cell.setCellStyle(helper.mNumberStyle);
        }
    }

    /**
     * 样式帮助类
     *
     * @author 蓝色守卫
     */
    class CellHelper {
        private HSSFFont mFontStyle;// 字体的样式
        private CellStyle mNumberStyle;// 数字的样式
        private CellStyle mWrittenStyle;// 文字的样式

        public CellHelper(Workbook wb) {
            super();
            // 字体的样式
            this.mFontStyle = (HSSFFont) wb.createFont();
            this.mFontStyle.setFontName("Microsoft YaHei");
            // 数字的样式
            this.mNumberStyle = wb.createCellStyle();
            this.mNumberStyle.setFillForegroundColor((short) 2);// 设置背景色
            this.mNumberStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            this.mNumberStyle.setFont(mFontStyle);
            // 文字的样式
            this.mWrittenStyle = wb.createCellStyle();
            this.mWrittenStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 水平居中
            this.mWrittenStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
            this.mWrittenStyle.setFont(mFontStyle);
        }

        public HSSFFont getmFontStyle() {
            return mFontStyle;
        }

        public void setmFontStyle(HSSFFont mFontStyle) {
            this.mFontStyle = mFontStyle;
        }

        public CellStyle getmNumberStyle() {
            return mNumberStyle;
        }

        public void setmNumberStyle(CellStyle mNumberStyle) {
            this.mNumberStyle = mNumberStyle;
        }

        public CellStyle getmWrittenStyle() {
            return mWrittenStyle;
        }

        public void setmWrittenStyle(CellStyle mWrittenStyle) {
            this.mWrittenStyle = mWrittenStyle;
        }
    }

    public static void main(String[] args) {
//        Rectangle rectangle1 = new Rectangle(0, 0, 10, 10);
//        Rectangle rectangle2 = new Rectangle(1, 0, 9, 10);
//        boolean contains = rectangle1.contains(rectangle2);

        // String jsonstring =
        // "[{\"isValid\":\"1\",\"name\":\"\",\"username\":\"123\",\"password\":\"345345\",\"connectString\":\"4535\",\"options\":\"\"},{\"isValid\":\"1\",\"name\":\"\",\"username\":\"123\",\"password\":\"345345\",\"connectString\":\"4535\",\"options\":\"\"}]";
        String jsonstring = "" +
                "[{\"name111\":\"1节\",\"code1\":\"010101\",\"name2\":\"2节\",\"code2\":\"010102\",\"name3\":\"3节\",\"code3\":\"010103\"},"
                + "{\"name1\":\"1节\",\"code1\":\"010101\",\"name2\":\"2节\",\"code2\":\"010102\",\"name3\":\"3节\",\"code3\":\"010103\"},"
                + "{\"name1\":\"1节\",\"code1\":\"010101\",\"name2\":\"2节\",\"code2\":\"010102\",\"name3\":\"3节\",\"code3\":\"010103\"},"
                + "{\"name1\":\"1节\",\"code1\":\"010101\",\"name2\":\"2节\",\"code2\":\"010102\",\"name3\":\"3节\",\"code3\":\"010103\"}]";

        List<LinkedHashMap> json = JSONArray.parseArray(jsonstring, LinkedHashMap.class);
//        JSONArray json = (JSONArray) linkedHashMaps;
        ExcelUtil ioUtil = new ExcelUtil();
        File file = new File("D:\\test.xls");
        if (!file.exists()) {
            try {
                file.createNewFile();
                System.out.println("----------");
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        try (FileOutputStream out = new FileOutputStream(file)) {
            ioUtil.exportExcel(json, out);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static String filterHtml(String str) {
        Pattern pattern = Pattern.compile(regxForHtml);
        Matcher matcher = pattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        boolean result1 = matcher.find();
        while (result1) {
            matcher.appendReplacement(sb, "");
            result1 = matcher.find();
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    /**
     * 判断是不是数字
     *
     * @param str
     * @return boolean
     */

    public static boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("[0-9]*");
        return pattern.matcher(str).matches();
    }


}
